DECLARE @role_name sysname

SET @role_name = N'BatchNoticeSendRole'

-- Create the role
IF NOT EXISTS (SELECT 1 FROM NavigantPortal.dbo.sysusers WHERE [name] = @role_name)
BEGIN
	-- Add the role to the NavigantPortal database
	EXEC sp_addrole @rolename = @role_name
END

-- Add the application pool users to the role
EXEC sp_grantdbaccess @loginame = N'NT AUTHORITY\NETWORK SERVICE'
EXEC sp_addrolemember @rolename = @role_name, @membername = N'NT AUTHORITY\NETWORK SERVICE'

-- Create the SMTP SQL user account
EXEC sp_AddSqlUserLogin N'NavigantSMTP', N'P@ssw0rd!23', @role_name, N'NavigantPortal'

GO